﻿using Models;
using MovieMS_JW4;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
  public  class BackstagePaging
    {
        //查用户数据//模糊查用户数据
        public static List<UserInfor> userInforsAll(int ID,String UName)
        {

            List<UserInfor> list = new List<UserInfor>();
            string sql = String.Format("select top 5 * from UserInfor where UserID not in(select top {0} UserID from UserInfor) and UName like '%{1}%'",ID, UName);
            SqlDataReader dr = DBHelperSQL.GetDataReader(sql);
            while (dr.Read())
            {
                UserInfor user = new UserInfor();
                user.UsersID = dr.GetInt32(0);
                user.UName = dr.GetString(1);
                user.UsersName = dr.GetString(2);
                user.UsersPwd = dr.GetString(3);
                user.UsersAge = dr.GetInt32(4);
                user.UsersSex = dr.GetString(5);
                user.UsersPostbox = dr.GetString(6);
                user.UsersPhone = dr.GetString(7);
                user.Address = dr.GetString(8);
                user.LogonDate = dr.GetDateTime(9);

                list.Add(user);

            }
            dr.Close();
            return list;
        }
        //商品的查询
        public static DataTable GoodsAll1(int ID, String GoodsName)
        {
            DBHelperSQL db = new DBHelperSQL();
            String sql = String.Format("select top 5 dbo.Goods.GoodsID, dbo.Goods.GoodsName, dbo.GoodsType.GoodsTypeName,dbo.Goods.GoodsPrice,dbo.Goods.GoodsDesc, dbo.Goods.GoodsNum, dbo.Goods.CreateTime from dbo.Goods INNER JOIN dbo.GoodsType ON dbo.Goods.GoodsTypeID = dbo.GoodsType.GoodsTypeID where GoodsID not in(select top {0} GoodsID from dbo.Goods INNER JOIN dbo.GoodsType ON dbo.Goods.GoodsTypeID = dbo.GoodsType.GoodsTypeID) and GoodsName like '%{1}%'",ID, GoodsName);
                
                DataTable dt = DBHelperSQL.GetDataTable(sql);
            dt.Clone();
            return dt;
          
        }

        //模糊查订单
        public static DataTable OrdersAll(int ID, String Phone)
        {
            DBHelperSQL db = new DBHelperSQL();
            String sql = String.Format("select top 5 dbo.Orders.OrdersID, dbo.UserInfor.UName, dbo.Goods.GoodsName, dbo.Orders.OredersNum, dbo.Goods.GoodsPrice, dbo.UserInfor.UserPhone, dbo.UserInfor.Address, dbo.Logistics.LogisticsName, dbo.Orders.OrdersTime from dbo.Logistics INNER JOIN dbo.Orders ON dbo.Logistics.LogisticsID = dbo.Orders.LogisticsID INNER JOIN dbo.UserInfor ON dbo.Orders.UserID = dbo.UserInfor.UserID INNER JOIN dbo.Goods ON dbo.Orders.GoodsID = dbo.Goods.GoodsID where OrdersID not in (select top {0} OrdersID from dbo.Logistics INNER JOIN dbo.Orders ON dbo.Logistics.LogisticsID = dbo.Orders.LogisticsID INNER JOIN dbo.UserInfor ON dbo.Orders.UserID = dbo.UserInfor.UserID INNER JOIN dbo.Goods ON dbo.Orders.GoodsID = dbo.Goods.GoodsID)and UserPhone like '%{1}%'", ID, Phone);
            DataTable dt = DBHelperSQL.GetDataTable(sql);
            dt.Clone();
            return dt;
        }
    }
}
